package fr.inserm.u674.chcdbweb.model.DAO;

import static fr.inserm.u674.chcdbweb.model.DAO.DAOUtil.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import fr.inserm.u674.chcdbweb.model.DTO.EAVModality;
import fr.inserm.u674.chcdbweb.model.DTO.EAVVariable;
import fr.inserm.u674.chcdbweb.model.DTO.Tissue;

public class EAVModalityDAO {

	// CONSTANTS

	private static final String SQL_LIST_BYTISSUE =
		"SELECT * FROM EAV_Modalities WHERE tissue_chcID = ? AND tissue_tissueTypeID = ?";
	private static final String SQL_LIST_BYVARIABLE =
		"SELECT * FROM EAV_Modalities WHERE EAV_Variables_variableID = ?";

	private static final String SQL_LIST_BYTISSUE_DISTINCTVALUES =
		"SELECT DISTINCT modality FROM EAV_Modalities WHERE tissue_chcID = ? AND tissue_tissueTypeID = ? "
			+ "ORDER BY modality";
	private static final String SQL_LIST_BYVARIABLE_DISTINCTVALUES =
		"SELECT DISTINCT modality FROM EAV_Modalities WHERE EAV_Variables_variableID = ? "
			+ "ORDER BY modality";

	private static final String SQL_FIND =
		"SELECT * FROM Modalities WHERE tissue_chcID = ? AND tissue_tissueTypeID = ? "
			+ "AND EAV_Variables_variableID = ?";
	private static final String SQL_FIND_BYVARNAME =
		"SELECT * FROM Modalities WHERE tissue_chcID = ? AND tissue_tissueTypeID = ? "
			+ "AND EAV_Variables_variableID = "
			+ "(SELECT variableID FROM EAV_Variables where variableName = ?) ";

	private static final String SQL_CREATE = "INSERT INTO EAV_Modalities "
		+ "(tissue_chcID, tissue_tissueTypeID, modality, isDeprecated, EAV_Variables_variableID) "
		+ "VALUES (?,?,?,?,?)";

	private static final String SQL_UPDATE =
		"UPDATE EAV_Modalities SET modality = ? , isDeprecated = ?"
			+ " WHERE (EAV_Variables_variableID = ? "
			+ " AND tissue_chcID = ? AND tissue_tissueTypeID = ? ) ";

	private static final String SQL_UPDATE_BYVARIABLENAME_AND_MODALITYVALUE =
		"UPDATE EAV_Modalities SET modality = ?  WHERE EAV_Variables_variableID = "
			+ " ? AND modality = ? ";

	private static final String SQL_EXISTS =
		"SELECT COUNT(*) FROM EAV_Modalities WHERE tissue_chcID = ? "
			+ "AND tissue_tissueTypeID = ? AND EAV_Variables_variableID = ? ";
	private static final String SQL_EXISTS_WITHVALUE =
		"SELECT COUNT(*) FROM EAV_Modalities WHERE tissue_chcID = ? "
			+ "AND tissue_tissueTypeID = ? AND EAV_Variables_variableID = ?  AND modality  = ? ";

	private static final String SQL_DELETEALL_BY_VARIABLE =
		"DELETE FROM EAV_Modalities WHERE EAV_Variables_variableID = ? ";

	private static final String SQL_DELETE =
		"DELETE FROM EAV_Modalities WHERE EAV_Variables_variableID = ? "
			+ "AND tissue_chcID = ? AND tissue_tissueTypeID = ? ";

	// FIELDS

	private DAOFactory daoFactory;

	// CONSTRUCTOR

	public EAVModalityDAO(DAOFactory daoFactory) {
		this.daoFactory = daoFactory;
	}

	// ACTIONS

	/**
	 * This method retrieves all the eav modalities associated with a given tissue.
	 * 
	 * @param tissue
	 * @return
	 * @throws DAOException
	 */
	public List<EAVModality> list(Tissue tissue) throws DAOException {
		return list(SQL_LIST_BYTISSUE, tissue.getChcID(), tissue.getTissueTypeID());
	}

	/**
	 * This method retrieves all the eav modalities associated with a given eav variable.
	 * 
	 * @param variable
	 * @return
	 * @throws DAOException
	 */
	public List<EAVModality> list(EAVVariable variable) throws DAOException {
		return list(SQL_LIST_BYVARIABLE, variable.getVariableID());
	}

	public List<String> listDistinctValues(EAVVariable variable) throws DAOException {
		return listDistinctValues(SQL_LIST_BYVARIABLE_DISTINCTVALUES, variable.getVariableID());
	}

	public List<String> listDistinctValues(Tissue tissue) throws DAOException {
		return listDistinctValues(SQL_LIST_BYTISSUE_DISTINCTVALUES, tissue.getChcID(), tissue
			.getTissueTypeID());
	}

	private List<String> listDistinctValues(String sql, Object... values) throws DAOException {
		List<String> distinctModalityValues = new ArrayList<String>();
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = daoFactory.getConnection();
			statement = prepareStatement(connection, sql, false, values);
			resultSet = statement.executeQuery();
			while (resultSet.next())
				distinctModalityValues.add(resultSet.getString(1));
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		return distinctModalityValues;
	}

	private List<EAVModality> list(String sql, Object... values) throws DAOException {
		List<EAVModality> list = new ArrayList<EAVModality>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {

			connection = daoFactory.getConnection();
			preparedStatement = prepareStatement(connection, sql, false, values);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				list.add(mapModality(resultSet));
			}
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, preparedStatement, resultSet);
		}

		return list;
	}

	public EAVModality find(Tissue tissue, EAVVariable variable) throws DAOException {
		return find(SQL_FIND, tissue.getChcID(), tissue.getTissueTypeID(), variable.getVariableID());
	}

	public EAVModality find(Tissue tissue, String variableName) throws DAOException {
		return find(SQL_FIND_BYVARNAME, tissue.getChcID(), tissue.getTissueTypeID(), variableName);
	}

	private EAVModality find(String sql, Object... values) throws DAOException {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = daoFactory.getConnection();
			statement = prepareStatement(connection, sql, false, values);
			resultSet = statement.executeQuery();
			if (resultSet.next())
				return mapModality(resultSet);
			else
				throw new DAOException("No such modality.");
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, statement, resultSet);
		}
	}

	public void create(EAVModality modality) throws DAOException {
		Object[] values =
			{ modality.getTissue().getChcID(), modality.getTissue().getTissueTypeID(),
				modality.getModality(), modality.isDeprecated(),
				modality.getEavVariable().getVariableID() };

		create(SQL_CREATE, values);
	}

	private void create(String sql, Object... values) throws DAOException {
		Connection connection = null;
		PreparedStatement statement = null;
		int affectedRows = 0;
		try {
			connection = daoFactory.getConnection();
			statement = prepareStatement(connection, sql, false, values);
			affectedRows = statement.executeUpdate();
			if (affectedRows == 0)
				throw new DAOException("Modality creation failed, no rows affected in database.");
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, statement);
		}
	}

	public void update(EAVModality modality) throws DAOException {
		Object[] values =
			{ modality.getModality(), modality.isDeprecated() ? 1
				: 0, modality.getEavVariable().getVariableID(), modality.getTissue().getChcID(),
				modality.getTissue().getTissueTypeID() };
		Connection connection = null;
		PreparedStatement statement = null;
		int affectedRows = 0;
		try {
			connection = daoFactory.getConnection();
			statement = prepareStatement(connection, SQL_UPDATE, false, values);
			affectedRows = statement.executeUpdate();
			if (affectedRows < 1)
				throw new DAOException("No rows affected in database for tissue "
					+ modality.getTissue() + ", variable " + modality.getEavVariable()
					+ " and modality " + modality.getModality() + ", something went wrong.");
			else if (affectedRows > 1)
				throw new DAOException("More than row was updated in database for tissue "
					+ modality.getTissue() + ", variable " + modality.getEavVariable()
					+ " and modality " + modality.getModality()
					+ ", this should NEVER happen, please contact the database administrator");
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, statement);
		}
	}

	/**
	 * This method updates all the modalities for a given variable and modality value, regardless of
	 * the Tissue involved
	 * 
	 * @param newModality
	 *            the new value that will be set to all modalities
	 * @param eavVariable
	 *            the EAV_Variable for which modalities will be updated
	 * @param oldModality
	 *            the old value of the modalities which will be replaced by newModality
	 * @return the number of rows (i.e. modalities) modified
	 * @throws DAOException
	 */
	public int updateByVariableNameAndModalityValue(String newModality, EAVVariable eavVariable,
		String oldModality) throws DAOException {
		Object[] values = { newModality, eavVariable.getVariableID(), oldModality };
		Connection connection = null;
		PreparedStatement statement = null;
		int affectedRows = 0;
		try {
			connection = daoFactory.getConnection();
			statement =
				prepareStatement(connection, SQL_UPDATE_BYVARIABLENAME_AND_MODALITYVALUE, false,
					values);
			affectedRows = statement.executeUpdate();
			if (affectedRows > 0)
				return affectedRows;
			else
				throw new DAOException("No rows updated in the database for variable "
					+ eavVariable + " old modality " + oldModality + " and new modality "
					+ newModality);
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, statement);
		}
	}

	public void save(EAVModality modality) throws DAOException {
		if (exists(modality, false))
			update(modality);
		else
			create(modality);
	}

	public Boolean exists(EAVModality modality, Boolean compareModalityValue) throws DAOException {
		if (compareModalityValue) {
			Object[] values =
				{ modality.getTissue().getChcID(), modality.getTissue().getTissueTypeID(),
					modality.getEavVariable().getVariableID(), modality.getModality() };
			return exists(modality, SQL_EXISTS_WITHVALUE, values);
		}
		else {
			Object[] values =
				{ modality.getTissue().getChcID(), modality.getTissue().getTissueTypeID(),
					modality.getEavVariable().getVariableID() };
			return exists(modality, SQL_EXISTS, values);
		}

	}

	/**
	 * This function determines wether an EAVModality exists (for a given Tissue and EAVVariable),
	 * 
	 * @param modality
	 * @return
	 * @throws DAOException
	 */
	private Boolean exists(EAVModality modality, String sql, Object[] values) throws DAOException {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			connection = daoFactory.getConnection();
			statement = prepareStatement(connection, sql, false, values);
			resultSet = statement.executeQuery();
			resultSet.next();
			if (resultSet.getInt(1) < 1)
				return false;
			else if (resultSet.getInt(1) == 1)
				return true;
			else
				throw new DAOException("Database inconsistency, there are more than"
					+ " one row in the modality table for the given parameters");
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, statement, resultSet);
		}
	}

	public int deleteVariableModalities(EAVVariable eavVariable) throws DAOException {
		Object[] values = { eavVariable.getVariableID() };
		Connection connection = null;
		PreparedStatement statement = null;
		int nRowsModalitiesDeleted = 0;
		try {
			connection = daoFactory.getConnection();
			statement = prepareStatement(connection, SQL_DELETEALL_BY_VARIABLE, false, values);
			nRowsModalitiesDeleted = statement.executeUpdate();
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, statement);
		}
		return nRowsModalitiesDeleted;
	}

	public int delete(EAVModality mod) throws DAOException {
		Object[] values =
			{ mod.getEavVariable().getVariableID(), mod.getTissue().getChcID(),
				mod.getTissue().getTissueTypeID() };
		Connection connection = null;
		PreparedStatement statement = null;
		int nRowsModalitiesDeleted = 0;
		try {
			connection = daoFactory.getConnection();
			statement = prepareStatement(connection, SQL_DELETE, false, values);
			nRowsModalitiesDeleted = statement.executeUpdate();
		}
		catch (SQLException e) {
			throw new DAOException(e);
		}
		finally {
			close(connection, statement);
		}
		return nRowsModalitiesDeleted;
	}

	public void duplicateModalities(Tissue originalTissue, Tissue newTissue, String... masks)
		throws DAOException {
		List<EAVModality> originalModalities = list(originalTissue);
		for (EAVModality oriMod : originalModalities) {
			boolean proceed = false;
			String varName = oriMod.getEavVariable().getVariableName();
			for(int i=0; i<masks.length;i++){
				if(varName.indexOf(masks[i]) == 0)
					proceed = true;
			}
			
			if (proceed) {
				EAVModality newMod =
					new EAVModality(oriMod.getEavVariable(), newTissue, oriMod.getModality());
				save(newMod);
			}

		}
	}

	// UTILITIES

	protected static EAVModality mapModality(ResultSet resultSet) throws SQLException {
		return new EAVModality(
			new EAVVariable(resultSet.getLong("EAV_Variables_variableID")),
			new Tissue(resultSet.getInt("tissue_chcID"), resultSet.getString("tissue_tissueTypeID")),
			resultSet.getString("modality"), resultSet.getInt("isDeprecated") != 0);
	}
}
